package io.github.jsp.tranvolume.mapper;

import io.github.jsp.tranvolume.entity.TranVolumeTotal;
import org.apache.ibatis.annotations.*;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

/**
 * 全量交易统计Mapper
 */
@Mapper
public interface TranVolumeTotalMapper {

    @Select("SELECT * FROM tran_volume_total WHERE minute_time BETWEEN #{startTime} AND #{endTime} ORDER BY minute_time DESC")
    List<TranVolumeTotal> findByTimeRange(@Param("startTime") LocalDateTime startTime, 
                                         @Param("endTime") LocalDateTime endTime);

    @Select("SELECT * FROM tran_volume_total WHERE minute_time BETWEEN #{startTime} AND #{endTime} " +
            "AND center = #{center} ORDER BY minute_time DESC")
    List<TranVolumeTotal> findByTimeRangeAndCenter(@Param("startTime") LocalDateTime startTime, 
                                                  @Param("endTime") LocalDateTime endTime,
                                                  @Param("center") String center);

    @Select("SELECT center, SUM(total_count) as total_count, SUM(success_count) as success_count, SUM(fail_count) as fail_count " +
            "FROM tran_volume_total WHERE minute_time BETWEEN #{startTime} AND #{endTime} " +
            "GROUP BY center ORDER BY total_count DESC")
    List<Map<String, Object>> getCenterVolumeStats(@Param("startTime") LocalDateTime startTime, 
                                                   @Param("endTime") LocalDateTime endTime);

    @Insert("INSERT INTO tran_volume_total (minute_time, center, service_plat, outsys_code, chel_code, total_count, success_count, fail_count) " +
            "VALUES (#{minuteTime}, #{center}, #{servicePlat}, #{outsysCode}, #{chelCode}, #{totalCount}, #{successCount}, #{failCount}) " +
            "ON CONFLICT (minute_time, center, service_plat, outsys_code, chel_code) DO UPDATE SET " +
            "total_count = total_count + #{totalCount}, " +
            "success_count = success_count + #{successCount}, " +
            "fail_count = fail_count + #{failCount}")
    int insertOrUpdate(TranVolumeTotal record);

    @Select("SELECT DATE_TRUNC('minute', minute_time) as minute_time, " +
            "center, SUM(total_count) as total_count, SUM(success_count) as success_count, SUM(fail_count) as fail_count " +
            "FROM tran_volume_total WHERE minute_time >= NOW() - INTERVAL '60 minutes' " +
            "GROUP BY DATE_TRUNC('minute', minute_time), center " +
            "ORDER BY minute_time DESC")
    List<Map<String, Object>> getRecentCenterData();

    @Select("SELECT DATE_TRUNC('minute', minute_time) as minute_time, " +
            "center, SUM(total_count) as total_count, SUM(success_count) as success_count " +
            "FROM tran_volume_total WHERE center = #{center} AND minute_time >= NOW() - INTERVAL '60 minutes' " +
            "GROUP BY DATE_TRUNC('minute', minute_time), center " +
            "ORDER BY minute_time DESC")
    List<Map<String, Object>> getRecentCenterDataByCenter(@Param("center") String center);
}